package util;

import pojo.Grade;
import pojo.Student;
import vo.StudentVO;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class StudentDBUtil extends DBUtil {
    List<String> list = new ArrayList<>();

    //根据学号删除学生
    public int deleteStuBySno(int sno) {
        int i = 0;
        try {
            getConn();
            String sql = "delete from student where sno=" + sno;
            st = conn.createStatement();
            i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("数据删除成功");
            } else {
                System.out.println("数据删除失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    //添加学生
    public int addStudent(Student stu) {
        int i = 0;
        try {
            getConn();
            String sql = "insert into student values('" + stu.getSname() + "','" + stu.getSex() + "'," + stu.getAge() + "," + stu.getGno() + "," + stu.getSno() + ")";
            //创建 statement
            st = conn.createStatement();
            //执行sql的 查询，得到结果。结果封装在 rs 中
            i = st.executeUpdate(sql);
            if (i > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    //根据sno查询学生
    public Student getStuBySno(Integer sno) {
        Student stu = null;
        try {
            getConn();
            String sql = "select * from student where sno=" + sno;
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if (rs.next()) {
                stu = new Student(rs.getInt(5),
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getInt(4));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return stu;
    }


    //更新学生字段
    public int updateStudent(Student stu) {
        int i = 0;
        try {
            getConn();
            st = conn.createStatement();
            String sql = getSql(stu);
            i = st.executeUpdate(sql);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    //生成动态sql
    public String getSql(Student stu) {
        StringBuffer sb = new StringBuffer("update student set");
        if (stu.getSname() != null) {
            sb.append(" sname=");
            sb.append("'");
            sb.append(stu.getSname());
            sb.append("',");
        }
        if (stu.getSex() != null) {
            sb.append(" sex=");
            sb.append("'");
            sb.append(stu.getSex());
            sb.append("',");
        }
        if (stu.getAge() != null) {
            sb.append(" age=");
            sb.append("'");
            sb.append(stu.getAge());
            sb.append("',");
        }
        if (stu.getGno() != null) {
            sb.append(" gno=");
            sb.append("'");
            sb.append(stu.getGno());
            sb.append("',");
        }

        sb.append(" where sno=");
        sb.append(stu.getSno());
        //获取最后一个,的下标
        int index = sb.lastIndexOf(",");
        sb.replace(index, index + 1, "");
        return sb.toString();
    }

    //查询学生对应的班级教师信息
    public List<StudentVO> getStusGradeAndTeacher() {
        //jdk 8 新特性 类型推断
        List<StudentVO> list = new ArrayList<>();
        try {
            getConn();
            String sql = "select s.sno 学号,s.sname 学生姓名,s.sex 学生性别,s.age 学生年龄,g.gname 班级,g.gdesc 班级描述,t.tname 教师名称" +
                    " from student s,teacher t,grade g where g.gno=t.gno and s.gno=t.gno";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                int s1 = rs.getInt(1);
                String s2 = rs.getString("学生姓名");
                String s3 = rs.getString("学生性别");
                int s4 = rs.getInt("学生年龄");
                String s5 = rs.getString("班级");
                String s6 = rs.getString("班级描述");
                String s7 = rs.getString("教师名称");
                StudentVO studentVO = new StudentVO(s1, s2, s3, s4, s5, s6, s7);
                list.add(studentVO);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return list;
    }

    //查询学生对应的班级教师信息 2
    public List<Student> getStusGradeAndTeacher2() {
        GradeDBUtil gradeDBUtil = new GradeDBUtil();
        TeacherDBUtil teacherDBUtil = new TeacherDBUtil();
        //jdk 8 新特性 类型推断
        List<Student> list = new ArrayList<>();
        try {
            getConn();
            String sql = "select * from student where gno is not null";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()) {
                //如果是多表联查  数据库中会关联主外键 消耗时间，效率慢
                //后期需要优化
                //使用数据库连接池，提高查询效率
                Grade grade = gradeDBUtil.getGradeByGno(rs.getInt("gno"));
                grade.setTeacher(teacherDBUtil.getTeacherByGno(grade.getGno()));
                int s1 = rs.getInt(5);
                String s2 = rs.getString(1);
                String s3 = rs.getString(2);
                int s4 = rs.getInt(3);
                int s5 = rs.getInt(4);

                Student student = new Student(s1, s2, s3, s4, s5, grade);
                list.add(student);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return list;
    }

    //登录方法
    public Student login(String sname, String psw) {
        Student student = null;
        try {
            getConn();
            String sql = "select * from student where sname='" + sname + "' and psw='" + psw + "'";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            if (rs.next()) {
                student = new Student(
                        rs.getInt(5),
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getInt(4)
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }

        return student;
    }

    public Student login2(String sname, String psw) {
        Student student = null;
        try {
            getConn();
            //? 是占位符
            String sql = "select * from student where sname=? and psw=?";
            ps = conn.prepareStatement(sql);
            //给参数赋值
            //1  2 分别表示两个占位符
            //防止sql注入
            ps.setObject(1, sname);
            ps.setObject(2, psw);
            System.out.println(sql);
            rs = ps.executeQuery();
            if (rs.next()) {
                student = new Student(
                        rs.getInt(5),
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getInt(4)
                );
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }

        return student;
    }

    //添加学生
    public void addStudent2(Student stu) {
        int i = 0;
        try {
            getConn();
            String sql = "insert into student values(?,?,?,?,test_id.nextval,?)";
            //创建 statement
            ps = conn.prepareStatement(sql);
            //执行sql的 查询，得到结果。结果封装在 rs 中

            ps.setObject(1, stu.getSname());
            ps.setObject(2, stu.getSex());
            ps.setObject(3, stu.getAge());
            ps.setObject(4, stu.getGno());
            ps.setObject(5, stu.getPsw());
            i = ps.executeUpdate();
            if (i > 0) {
                System.out.println("添加成功");
            } else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
    }

    //更新学生字段
    public int updateStudent2(Student stu) {
        int i = 0;
        try {
            getConn();
            String sql = getSql2(stu);
            ps = conn.prepareStatement(sql);
            int index = 1;
            for (String s : list) {
                if ("sname".equals(s)) {
                    ps.setObject(index, stu.getSname());
                    index++;
                }
                if ("sex".equals(s)) {
                    ps.setObject(index, stu.getSex());
                    index++;
                }
                if ("age".equals(s)) {
                    ps.setObject(index, stu.getAge());
                    index++;
                }
                if ("gno".equals(s)) {
                    ps.setObject(index, stu.getGno());
                    index++;
                }
                if ("psw".equals(s)) {
                    ps.setObject(index, stu.getPsw());
                    index++;
                }
            }
            i = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }
        return i;
    }

    public String getSql2(Student stu) {
        list.clear();
        StringBuffer sb = new StringBuffer("update student set");
        if (stu.getSname() != null) {
            list.add("sname");
            sb.append(" sname=?");
            sb.append(",");
        }
        if (stu.getSex() != null) {
            list.add("sex");
            sb.append(" sex=?");
            sb.append(",");
        }
        if (stu.getAge() != null) {
            list.add("age");
            sb.append(" age=?");

            sb.append(",");
        }
        if (stu.getGno() != null) {
            list.add("gno");
            sb.append(" gno=?");
            sb.append(",");
        }
        if (stu.getPsw() != null) {
            list.add("psw");
            sb.append(" psw=?");
            sb.append(",");
        }

        sb.append(" where sno=");
        sb.append(stu.getSno());
        //获取最后一个,的下标
        int index = sb.lastIndexOf(",");
        sb.replace(index, index + 1, "");
        System.out.println(sb);
        return sb.toString();
    }

    //通过反射获取类的属性
    public void updateStudent3(Student stu) {

        try {
            getConn();
            String sql = getSql2(stu);
            ps = conn.prepareStatement(sql);
            Class<? extends Student> aClass = stu.getClass();
            Field[] fields = aClass.getDeclaredFields();
            int index = 1;
            for (Field field : fields) {
                field.setAccessible(true);
                if (field.get(stu) != null) {
                    ps.setObject(index, field.get(stu));
                    if ("sno".equals(field.getName())) {
                        continue;
                    }
                    index++;
                }

            }
            ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConn();
        }

    }
}
